package com.yegs.jltqc.U8ServiceImpl;

import com.yegs.jltqc.TO.ImComePageQuery;
import com.yegs.jltqc.U8Service.U8InComeService;
import com.yegs.jltqc.common.StoreException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Service
public class U8InComeServiceImpl implements U8InComeService {

    @Autowired
    @Qualifier("u8JdbcTemplate")
    private JdbcTemplate u8JdbcTemplate;

    @Override
    public Page<Map<String, Object>> query(ImComePageQuery query) {
        String sql = "SELECT TOP " + (query.getPageIndex()) * query.getPageSize()+
                " ROW_NUMBER() OVER(ORDER BY id desc ) AS ROWID,* from qc_income where 1=1 ";
        if(query.getCinvname() != null && query.getCinvname().trim().length() > 0){
            sql += " and cInvName like '%"+query.getCinvname().trim()+"%'";
        }
        if(query.getCinvcode() != null && query.getCinvcode().trim().length() > 0){
            sql += " and cInvCode like '%"+query.getCinvcode().trim()+"%'";
        }
        if(query.getCwhname() != null && query.getCwhname().trim().length() > 0){
            sql += " and cwhname like '%"+query.getCwhname().trim()+"%'";
        }
        if(query.getSddate() != null){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql += " and ddate >= '"+sdf.format(query.getSddate())+"'";
        }
        if(query.getEddate() != null){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql += " and ddate <= '"+sdf.format(query.getEddate())+"'";
        }
        if(query.getDdate() != null){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql += " and ddate = '"+sdf.format(query.getDdate())+"'";
        }
        if(query.getCvenname() != null && query.getCvenname().trim().length() > 0){
            sql += " and cvenname like '%"+query.getCvenname().trim()+"%'";
        }
        if(query.getCcode() != null && query.getCcode().trim().length() > 0){
            sql += " and ccode like '%"+query.getCcode().trim()+"%'";
        }
        if(query.getCfree1() != null && query.getCfree1().trim().length() > 0){
            sql += " and cFree1 like '%"+query.getCfree1().trim()+"%'";
        }
        sql += " order by id desc ";
        sql = "select * from ("+sql +") as temp1 where ROWID > " + (query.getPageIndex()-1) * query.getPageSize();
        List<Map<String, Object>> list = u8JdbcTemplate.queryForList(sql);
        sql = "select count (*) from qc_income where 1=1 ";
        if(query.getCinvname() != null && query.getCinvname().trim().length() > 0){
            sql += " and cInvName like '%"+query.getCinvname().trim()+"%'";
        }
        if(query.getCinvcode() != null && query.getCinvcode().trim().length() > 0){
            sql += " and cInvCode like '%"+query.getCinvcode().trim()+"%'";
        }
        if(query.getSddate() != null){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql += " and ddate >= '"+sdf.format(query.getSddate())+"'";
        }
        if(query.getEddate() != null){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql += " and ddate <= '"+sdf.format(query.getEddate())+"'";
        }
        if(query.getCwhname() != null && query.getCwhname().trim().length() > 0){
            sql += " and cwhname like '%"+query.getCwhname().trim()+"%'";
        }
        if(query.getCsocode() != null && query.getCsocode().trim().length() > 0){
            sql += " and cSOCode like '%"+query.getCsocode().trim()+"%'";
        }
        if(query.getDdate() != null){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql += " and ddate = '"+sdf.format(query.getDdate())+"'";
        }
        if(query.getCvenname() != null && query.getCvenname().trim().length() > 0){
            sql += " and cvenname like '%"+query.getCvenname().trim()+"%'";
        }
        if(query.getCcode() != null && query.getCcode().trim().length() > 0){
            sql += " and ccode like '%"+query.getCcode().trim()+"%'";
        }
        if(query.getCfree1() != null && query.getCfree1().trim().length() > 0){
            sql += " and cFree1 like '%"+query.getCfree1().trim()+"%'";
        }
        int total = u8JdbcTemplate.queryForObject(sql,Integer.class);
        PageRequest request = PageRequest.of(query.getPageIndex() -1,query.getPageSize());
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        list.forEach(map -> { map.put("dDate",sdf.format((Date) map.get("dDate"))); });
        PageImpl page = new PageImpl(list,request,total);
        return page;
    }

    @Override
    public List<Map<String, Object>> findAll(ImComePageQuery query) {
        String sql = "SELECT TOP 100 * from qc_income where 1=1 ";
        if(query.getCinvname() != null && query.getCinvname().trim().length() > 0){
            sql += " and cInvName like '%"+query.getCinvname().trim()+"%'";
        }
        if(query.getCinvcode() != null && query.getCinvcode().trim().length() > 0){
            sql += " and cInvCode like '%"+query.getCinvcode().trim()+"%'";
        }
        if(query.getCwhname() != null && query.getCwhname().trim().length() > 0){
            sql += " and cwhname like '%"+query.getCwhname().trim()+"%'";
        }
        if(query.getSddate() != null){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql += " and ddate >= '"+sdf.format(query.getSddate())+"'";
        }
        if(query.getEddate() != null){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql += " and ddate <= '"+sdf.format(query.getEddate())+"'";
        }
        if(query.getDdate() != null){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql += " and ddate = '"+sdf.format(query.getDdate())+"'";
        }
        if(query.getCvenname() != null && query.getCvenname().trim().length() > 0){
            sql += " and cvenname like '%"+query.getCvenname().trim()+"%'";
        }
        if(query.getCcode() != null && query.getCcode().trim().length() > 0){
            sql += " and ccode like '%"+query.getCcode().trim()+"%'";
        }
        if(query.getCfree1() != null && query.getCfree1().trim().length() > 0){
            sql += " and cFree1 like '%"+query.getCfree1().trim()+"%'";
        }
        sql += " order by id desc ";
        List<Map<String, Object>> list = u8JdbcTemplate.queryForList(sql);
        return  list;
    }

    public List<Map<String, Object>> findAllByProduce(ImComePageQuery query) {
        String sql = "SELECT TOP 100 * from ymh_selectpo_qc where 1=1 ";
        if(query.getCinvname() != null && query.getCinvname().trim().length() > 0){
            sql += " and cInvName like '%"+query.getCinvname().trim()+"%'";
        }
        if(query.getCinvcode() != null && query.getCinvcode().trim().length() > 0){
            sql += " and cInvCode like '%"+query.getCinvcode().trim()+"%'";
        }
        if(query.getCwhname() != null && query.getCwhname().trim().length() > 0){
            sql += " and cwhname like '%"+query.getCwhname().trim()+"%'";
        }
        if(query.getSddate() != null){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql += " and ddate >= '"+sdf.format(query.getSddate())+"'";
        }
        if(query.getEddate() != null){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql += " and ddate <= '"+sdf.format(query.getEddate())+"'";
        }
        if(query.getDdate() != null){
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql += " and ddate = '"+sdf.format(query.getDdate())+"'";
        }
        if(query.getCvenname() != null && query.getCvenname().trim().length() > 0){
            sql += " and cvenname like '%"+query.getCvenname().trim()+"%'";
        }
        if(query.getCcode() != null && query.getCcode().trim().length() > 0){
            sql += " and ccode like '%"+query.getCcode().trim()+"%'";
        }
        if(query.getCfree1() != null && query.getCfree1().trim().length() > 0){
            sql += " and cFree1 like '%"+query.getCfree1().trim()+"%'";
        }
        sql += " order by id desc ";
        List<Map<String, Object>> list = u8JdbcTemplate.queryForList(sql);
        return  list;
    }

    @Override
    public Map<String, Object> get(Long id) {
        String sql = "select * from qc_income where autoid = ? ";
        Map<String, Object> map = u8JdbcTemplate.queryForMap(sql, id);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        map.put("dDate",sdf.format((Date) map.get("dDate")));
        return map;
    }

    @Override
    public void finishByAutoid(Long autoid) {
        String sql = "update rdrecords01_extradefine set cBDefine13 = '已质检' where autoid = ? ";
        int update = u8JdbcTemplate.update(sql, autoid);
        if(update != 1){
            throw new StoreException("单据错误");
        }
    }


}
